procedures and triggers:

===========================
CREATE TRIGGER `tr_notifyfriendactions` AFTER INSERT ON `af_user_friends`
 FOR EACH ROW begin
   if new.Status=3 then
    INSERT into af_notifications set User_ID=new.User_ID, Record_ID=new.Unique_User_Friend_ID,Action_Type='Friend_Request',Module='User',Privacy_Type_ID=3,Table_Name='af_user_friends',Table_Field_ID='Unique_User_Friend_ID',Activity_Date=new.requestdate,Friend_IDs=new.User_Friend_ID;
  end if;
end

CREATE TRIGGER `tr_postactivities` AFTER INSERT ON `af_user_posts_activities`
 FOR EACH ROW BEGIN
IF new.Shared_Post_ID>0 THEN /* new post entry for sharing */
BEGIN
/* mark an entry in af_user_post_shares */
insert into af_user_post_shares(Post_ID,Source_Table_Name,Source_Table_PKEY,Source_Activity_User_Field,User_ID,Share_Date) values(new.Shared_Post_ID,'af_user_posts_activities','Post_ID','User_ID',new.User_ID,new.Posted_On_Date);
END;
END IF;
END


CREATE TRIGGER `tr_postlikes` AFTER INSERT ON `af_user_post_likes`
 FOR EACH ROW BEGIN
CALL getOriginUserIDForPostActivity(new.Post_ID,new.Source_Table_Name,new.Source_Activity_User_ID,new.Source_Table_PKEY,@activitymainownerid);
CALL getDefaultPrivacySettingForUser(new.User_ID,@userprivacytype);
INSERT into af_notifications
(Record_Owner_ID,User_ID,Record_ID,Action_Type,Module,Source_Record_ID,Privacy_Type_ID,Table_Name,Table_Field_ID,Activity_Date,Status) 
values
(@activitymainownerid,new.User_ID,new.Likes_ID,'Like','NewsFeeds',new.Post_ID,@userprivacytype,'af_user_post_likes','Likes_ID',new.Like_Date,'ACTIVE');

CALL getTotalLikeCountsForFeeds(new.Post_ID,@ucounts);
update af_user_posts_activities set Total_Likes=@ucounts where Post_ID=new.Post_ID;

END

CREATE TRIGGER `tr_postshared` AFTER INSERT ON `af_user_post_shares`
 FOR EACH ROW BEGIN
CALL getOriginUserIDForPostActivity(new.Post_ID,new.Source_Table_Name,new.Source_Activity_User_Field,new.Source_Table_PKEY,@activitymainownerid);
CALL getDefaultPrivacySettingForUser(new.User_ID,@userprivacytype);
INSERT into af_notifications
(Record_Owner_ID,User_ID,Record_ID,Action_Type,Module,Source_Record_ID,Privacy_Type_ID,Table_Name,Table_Field_ID,Activity_Date,Status) 
values
(@activitymainownerid,new.User_ID,new.Share_ID,'Share','NewsFeeds',new.Post_ID,@userprivacytype,'af_user_post_shares','Share_ID',new.Share_Date,'ACTIVE');

/* it is not working because the trigger is called while inserting record in post activity*/
/*Can't update table 'af_user_posts_activities' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.*/
/*
CALL getTotalShareCountsForFeeds(new.Post_ID,@ucounts);
update af_user_posts_activities set Total_Shares=@ucounts where Post_ID=new.Post_ID;
*/

END


CREATE TABLE IF NOT EXISTS `af_user_post_shares` (
  `Share_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Post_ID` int(11) NOT NULL,
  `Source_Table_Name` varchar(100) NOT NULL,
  `Source_Table_PKEY` varchar(100) NOT NULL,
  `Source_Activity_User_Field` varchar(100) NOT NULL,
  `User_ID` int(11) NOT NULL,
  `Share_Date` int(30) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Share_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;


CREATE DEFINER=`root`@`localhost` PROCEDURE `getTotalLikeCountsForFeeds`(IN `postid` INT(11), OUT `ucounts` INT(11))
    READS SQL DATA
    COMMENT 'to fetch total likes for post'
select IFNULL(count(Likes_ID),0) as LikeCount into ucounts from af_user_post_likes where Post_ID=postid

CREATE DEFINER=`root`@`localhost` PROCEDURE `getTotalShareCountsForFeeds`(IN `postid` INT(11), OUT `ucounts` INT(11))
    READS SQL DATA
    COMMENT 'to fetch total shares for post'
select IFNULL(count(Share_ID),0) as ShareCount into ucounts from af_user_post_shares where Post_ID=postid


getDefaultPrivacySettingForUser
----------------------------------
CREATE DEFINER=`root`@`localhost` PROCEDURE `getDefaultPrivacySettingForUser`(IN `userid` INT(11) ZEROFILL, OUT `privacytype` INT(11) ZEROFILL)
    READS SQL DATA
    COMMENT 'Procedure to get user default privacy setting regarding feed etc'
SELECT 
	Default_Setting INTO privacytype 
FROM 
	af_user_privacy_settings 
WHERE User_ID=userid


getOriginUserIDForPostActivity
---------------------------------
CREATE DEFINER=`root`@`localhost` PROCEDURE `getOriginUserIDForPostActivity`(IN `recordid` INT(11) ZEROFILL, IN `activitytablename` VARCHAR(100), IN `returnfield` VARCHAR(100), IN `pkeyfield` VARCHAR(100), OUT `owneruserid` INT(11) ZEROFILL)
    READS SQL DATA
    COMMENT 'Procedure To GET Owner ID of the activity whose post is going to like'
SELECT 
	User_ID INTO owneruserid 
FROM 
	af_user_posts_activities 
WHERE POST_ID=recordid

getTotalLikeCountsForFeeds
----------------------------------
CREATE DEFINER=`root`@`localhost` PROCEDURE `getTotalLikeCountsForFeeds`(IN `postid` INT(11), OUT `ucounts` INT(11))
    READS SQL DATA
    COMMENT 'to fetch total likes for post'
select IFNULL(count(Likes_ID),0) as LikeCount into ucounts from af_user_post_likes where Post_ID=postid

getUniqueFeedIDToNotify
----------------------------------
CREATE DEFINER=`root`@`localhost` PROCEDURE `getUniqueFeedIDToNotify`(IN `lastNotificationRead` INT(11) ZEROFILL, IN `userid` INT(11) ZEROFILL)
    READS SQL DATA
    COMMENT 'To fetch only those notifications meant for me'
select distinct(Source_Record_ID) from af_notifications where Status = 'Active' and Notification_ID > lastNotificationRead
and 
( 
    CASE 
        WHEN Privacy_Type_ID =1 THEN 
                (
                SELECT count(user_id) 
                FROM `af_users` 
                WHERE 
                    user_id=Record_Owner_ID and 
                    FIND_IN_SET(Userid,`Active_Friends_IDs` ) and 
                    not FIND_IN_SET(Userid,`Blocked_Friends_IDs` )
                ) = 1 
                
                OR 
                
                (
                select count(Follower_ID) 
                from af_source_followers 
                where 
                    User_ID=Userid and 
                    source_id=Record_Owner_ID and 
                    source_type='PROFILE' 
                ) = 1 
                
                OR 
                
                (
                select count(Post_ID) 
                from af_user_posts_activities 
                where 
                    post_id=record_id and 
                    Friend_ids in (select Active_Friends_IDs from af_users where user_id=Userid)
                ) = 1 
                
        WHEN Privacy_Type_ID =2 THEN 
            (
            SELECT count(user_id) 
            FROM `af_users` 
            WHERE user_id=Record_Owner_ID and 
            FIND_IN_SET(Userid,`Active_Friends_IDs` ) and 
            not FIND_IN_SET(Userid,`Blocked_Friends_IDs` )
            ) = 1 
            
        WHEN Privacy_Type_ID =3 THEN 
            (
            select count(Post_ID) 
            from af_user_posts_activities 
            where 
                post_id=record_id and 
                Friend_ids in (select Active_Friends_IDs from af_users where user_id=Userid)
            ) = 1 
            
        WHEN Privacy_Type_ID =4 THEN 
            (
            select count(Friend_ID) 
            from af_cluster_friends 
            where Cluster_ID in (Select Cluster_ID from af_user_posts_activities where post_id=record_id ) and 
            status=1 and 
            friend_id=Userid
            ) = 1 
            
    ELSE 1 

END 

)
order by Notification_ID desc

getUserDetails
----------------------------
CREATE DEFINER=`root`@`localhost` PROCEDURE `getUserDetails`(IN `uid` INT(11))
    READS SQL DATA
    COMMENT 'used to fetch user details'
SELECT  `User_ID` AS uid,  `Allyfolio_User_ID` AS plink, IF(LENGTH(Profile_Pic_Path)>0,profile_pic_path,'images/default_user_pic.jpg') AS picpath,  `Full_Name` AS uname,  `Is_Celebrity` 
FROM  `af_users` 
WHERE  `User_ID` =uid

getWithoutFeedNotifications
-------------------------------------
CREATE DEFINER=`root`@`localhost` PROCEDURE `getWithoutFeedNotifications`(IN `lastNotificationRead` INT(11), IN `Userid` INT(11))
    READS SQL DATA
    COMMENT 'use to fetch all notifications'
select * from af_notifications where Status = 'Active' and Notification_ID > lastNotificationRead
and 
( 
    CASE 
        WHEN Module='User' THEN
    		Action_Type='Friend_Request' and FIND_IN_SET(4,`Friend_IDs` )>0
    ELSE 1 

END 

)
and Module<>'NewsFeeds'
order by Notification_ID desc




##############################################################################

TRIGGERS
=====================================

tr_notifyfriendactions
---------------------------
CREATE TRIGGER `tr_notifyfriendactions` AFTER INSERT ON `af_user_friends`
 FOR EACH ROW begin
   if new.Status=3 then
    INSERT into af_notifications set User_ID=new.User_ID, Record_ID=new.Unique_User_Friend_ID,Action_Type='Friend_Request',Module='User',Privacy_Type_ID=3,Table_Name='af_user_friends',Table_Field_ID='Unique_User_Friend_ID',Activity_Date=new.requestdate,Friend_IDs=new.User_Friend_ID;
  end if;
end

tr_postlikes
--------------------------
CREATE TRIGGER `tr_postlikes` AFTER INSERT ON `af_user_post_likes`
 FOR EACH ROW BEGIN
CALL getOriginUserIDForPostActivity(new.Post_ID,new.Source_Table_Name,new.Source_Activity_User_ID,new.Source_Table_PKEY,@activitymainownerid);
CALL getDefaultPrivacySettingForUser(new.User_ID,@userprivacytype);
INSERT into af_notifications
(Record_Owner_ID,User_ID,Record_ID,Action_Type,Module,Source_Record_ID,Privacy_Type_ID,Table_Name,Table_Field_ID,Activity_Date,Status) 
values
(@activitymainownerid,new.User_ID,new.Likes_ID,'Like','NewsFeeds',new.Post_ID,@userprivacytype,'af_user_post_likes','Likes_ID',new.Like_Date,'ACTIVE');

CALL getTotalLikeCountsForFeeds(new.Post_ID,@ucounts);
update af_user_posts_activities set Total_Likes=@ucounts where Post_ID=new.Post_ID;

END